import pandas as pd
import os
from collections import defaultdict

# 设置路径，当前目录下的文件夹
directory_path = 'data'

# 使用defaultdict创建空字典，用于储存身份证号信息
id_info = defaultdict(lambda: {
    '姓名': None,
    '手机号': None,
    '公司名称': None,
    '来自表格': set(),
    '出现次数': 0
})
# 身份证可能在表格中存在的列名
id_colum_names = ['身份证号', '身份证']
# 手机号可能在表格中存在的列名
phone_colum_names = ['订票手机号', '手机号码', '联系方式', '主要手机', '购票人']
# 公司名称可能在表格中存在的列名
company_colum_names = ['公司名称', '公司']

# 遍历文件夹中的所有文件
for file_name in os.listdir(directory_path):
    print('正在处理文件：', file_name)
    if file_name.endswith('.xlsx'):
        # 读取Excel文件      构建完整路径   目录路径+       文件名
        df = pd.read_excel(os.path.join(directory_path, file_name))
        id_colum_names = next((col for col in id_colum_names if col in df.columns), None)
        # 遍历Excel文件中的每一行
        if id_colum_names:
            phone_colum_names = next((col for col in phone_colum_names if col in df.columns), None)
            company_colum_names = next((col for col in company_colum_names if col in df.columns), None)

            for index, row in df.iterrows():
                id_number = row[id_colum_names]
                if pd.notna(id_number):
                    id_info[id_number]['姓名'] = row['姓名']
                    id_info[id_number]['出现次数'] += 1
                    id_info[id_number]['来自表格'].add(file_name)
                    id_info[id_number]['手机号'] = row[phone_colum_names] if pd.notna(row[phone_colum_names]) else id_info[id_number]['手机号']
                    id_info[id_number]['公司名称'] = row[company_colum_names] if pd.notna(row[company_colum_names]) else id_info[id_number]['公司名称']
        else:
            print(f'在{file_name}中未找到身份证号列，请检查列名是否正确')

result_df = pd.DataFrame.from_dict(
    ({k:{'姓名':v['姓名'],'手机号':v['手机号'],'公司名称':v['公司名称'],'来自表格':','.join(v['来自表格']),'出现次数':v['出现次数']} for k,v in id_info.items()}),
    orient='index'
).reset_index(drop=True)
print(result_df)

output_file_name = '身份证重复数据.xlsx'
result_df.to_excel(output_file_name, index=False)
print(f'结果已保存至{output_file_name}')
